﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.Serialization;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Data.SqlServerCe;

namespace DatabaseTools
{
    [Serializable]
    public class Column : ISerializable
    {
        public string Name { get; set; }
        public string DataType { get; set; }
        public bool Nullable { get; set; }
        public bool Identity { get; set; }
        public string Collation { get; set; }

        public Column() { }
        public Column(SerializationInfo info, StreamingContext ctxt)
        {
            Name = (string)info.GetValue("Name", typeof(string));
            DataType = (string)info.GetValue("DataType", typeof(string));
            Nullable = (bool)info.GetValue("Nullable", typeof(bool));
            Identity = (bool)info.GetValue("Identity", typeof(bool));
            Collation = (string)info.GetValue("Collation", typeof(string));
        }
        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("Name", Name);
            info.AddValue("DataType", DataType);
            info.AddValue("Nullable", Nullable);
            info.AddValue("Identity", Identity);
            info.AddValue("Collation", Collation);
        }

        public bool Compare(Column model)
        {
            if (this.DataType != model.DataType || this.Nullable != model.Nullable || this.Identity != model.Identity)
                return false;
            else
                return true;
        }
    }

    [Serializable]
    public class Index : ISerializable
    {
        public string Name { get; set; }
        public string ColumnNames { get; set; }
        public bool Clustered { get; set; }
        public bool Unique { get; set; }
        public bool PrimaryKey { get; set; }

        public Index() { }
        public Index(SerializationInfo info, StreamingContext ctxt)
        {
            Name = (string)info.GetValue("Name", typeof(string));
            ColumnNames = (string)info.GetValue("ColumnNames", typeof(string));
            Clustered = (bool)info.GetValue("Clustered", typeof(bool));
            Unique = (bool)info.GetValue("Unique", typeof(bool));
            PrimaryKey = (bool)info.GetValue("PrimaryKey", typeof(bool));
        }
        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("Name", Name);
            info.AddValue("ColumnNames", ColumnNames);
            info.AddValue("Clustered", Clustered);
            info.AddValue("Unique", Unique);
            info.AddValue("PrimaryKey", PrimaryKey);
        }
        public bool Compare(Index model)
        {
            if (this.ColumnNames != model.ColumnNames || this.Clustered != model.Clustered || this.Unique != model.Unique)
                return false;
            else
                return true;
        }
    }

    [Serializable]
    public class Table : ISerializable
    {
        List<Column> _columns = new List<Column>();
        List<Index> _indexes = new List<Index>();

        public List<Column> Columns { get { return _columns; } set { _columns = value; } }
        public List<Index> Indexes { get { return _indexes; } set { _indexes = value; } }
        public string Name { get; set; }

        public Table() { }
        public Table(SerializationInfo info, StreamingContext ctxt)
        {
            Columns = (List<Column>)info.GetValue("Columns", typeof(List<Column>));
            Indexes = (List<Index>)info.GetValue("Indexes", typeof(List<Index>));
            Name = (string)info.GetValue("Name", typeof(string));
        }
        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("Columns", Columns);
            info.AddValue("Indexes", Indexes);
            info.AddValue("Name", Name);
        }

        public Column FindColumn(string name)
        {
            foreach (Column col in this.Columns)
            {
                if (col.Name == name)
                    return col;
            }
            return null;
        }
        public Index FindIndex(string name)
        {
            foreach (Index idx in this.Indexes)
            {
                if (idx.Name == name)
                    return idx;
            }
            return null;
        }

        public IEnumerable<Column> ToInsertingColumn(Table model)
        {
            foreach (Column col in model.Columns)
            {
                Column c = FindColumn(col.Name);
                if (c == null)
                {
                    yield return col;
                }
            }
        }
        public IEnumerable<Column> ToChangingColumn(Table model)
        {
            foreach (Column col in model.Columns)
            {
                Column c = FindColumn(col.Name);
                if (c != null)
                {
                    if (!c.Compare(col)) // farklı
                        yield return col;
                }
            }
        }
        public IEnumerable<Column> ToDeletingColumn(Table model)
        {
            foreach (Column col in this.Columns)
            {
                Column c = model.FindColumn(col.Name);
                if (c == null)
                {
                    yield return col;
                }
            }
        }
        public IEnumerable<Column> ToCollationColumn(Table model)
        {
            foreach (Column col in this.Columns)
            {
                Column c = model.FindColumn(col.Name);
                if (c != null && c.Collation != col.Collation)
                {
                    yield return col;
                }
            }
        }

        public IEnumerable<Index> ToInsertingIndex(Table model)
        {
            foreach (Index idx in model.Indexes)
            {
                Index c = FindIndex(idx.Name);
                if (c == null)
                {
                    yield return idx;
                }
            }
        }
        public IEnumerable<Index> ToChangingIndex(Table model)
        {
            foreach (Index idx in model.Indexes)
            {
                Index c = FindIndex(idx.Name);
                if (c != null)
                {
                    if (!c.Compare(idx)) // farklı
                        yield return idx;
                }
            }
        }
        public IEnumerable<Index> ToDeletingIndex(Table model)
        {
            foreach (Index idx in this.Indexes)
            {
                Index c = model.FindIndex(idx.Name);
                if (c == null)
                {
                    yield return idx;
                }
            }
        }

    }

    [Serializable]
    public class Database : ISerializable
    {
        List<Table> _tables = new List<Table>();
        public List<Table> Tables
        {
            get { return _tables; }
            set { _tables = value; }
        }
        public int Version { get; set; }

        public Database() { }
        public Database(SerializationInfo info, StreamingContext ctxt)
        {
            Tables = (List<Table>)info.GetValue("Tables", typeof(List<Table>));
            Version = (int)info.GetValue("Version", typeof(int));
        }
        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("Tables", Tables);
            info.AddValue("Version", Version);
        }
        public Table FindTable(string name)
        {
            foreach (Table tbl in this.Tables)
            {
                if (tbl.Name == name)
                    return tbl;
            }
            return null;
        }
    }

    public interface IData
    {
        Database GetSchema();
        string GetVersion();

        bool AddTable(Table table);
        bool AddColumn(Table table, Column column);
        bool AddIndex(Table table, Index index);

        bool DropTable(Table table);
        bool DropColumn(Table table, Column column);
        bool DropIndex(Table table, Index index);

        bool ChangeColumn(Table table, Column column);
        bool ChangeIndex(Table table, Index index);
        bool ChangeCollation(Table table, Column column);
        bool ChangeVersion(int version);
    }

    public class Data
    {
        IData _instance;
        public Data(byte dbTip)
        {
            if (dbTip == 0)
            {
                _instance = new SqlServerTools();
            }
            else
            {
                _instance = new SqlCeTools();
            }
        }
        public void CreateSchema(string path)
        {
            Database db = _instance.GetSchema();
            if (db != null)
            {
                Tools.Serialize(path, db);
            }
        }
        public void UpdateDb(string path)
        {
            Database model = Tools.DeSerialize(path);
            Database real = _instance.GetSchema();
            bool basarili = false;
            if (model != null && real != null)
            {
                //if (real.Version < model.Version) // kullanılan veritabanının sürümü eski ise devam et
                //{
                foreach (Table tbl in model.Tables)
                {
                    Table t = real.FindTable(tbl.Name);
                    if (t == null)
                    {
                        _instance.AddTable(tbl); //tablo yoksa eklenir.
                        foreach (Index i in tbl.ToInsertingIndex(tbl))
                            basarili = _instance.AddIndex(tbl, i);
                    }
                    else //varsa karşılaştırmalar yapılır.
                    {
                        foreach (Column c in t.ToCollationColumn(tbl))
                            basarili = _instance.ChangeCollation(tbl, c);

                        foreach (Column c in t.ToInsertingColumn(tbl))
                            basarili = _instance.AddColumn(tbl, c);

                        foreach (Column c in t.ToChangingColumn(tbl))
                            basarili = _instance.ChangeColumn(tbl, c);

                        foreach (Column c in t.ToDeletingColumn(tbl))
                            basarili = _instance.DropColumn(tbl, c);

                        foreach (Index i in t.ToInsertingIndex(tbl))
                            basarili = _instance.AddIndex(tbl, i);

                        foreach (Index i in t.ToChangingIndex(tbl))
                            basarili = _instance.ChangeIndex(tbl, i);

                        foreach (Index i in t.ToDeletingIndex(tbl))
                            basarili = _instance.DropIndex(tbl, i);
                    }
                }

                foreach (Table tbl in real.Tables)
                {
                    Table t = model.FindTable(tbl.Name);
                    if (t == null) // tablo silinmiş model de yok gerçekten de sil
                    {
                        basarili = _instance.DropTable(tbl);
                    }
                }

                if (basarili) //başarılı olarak tamamlandıysa versiyon güncellenir
                {
                    _instance.ChangeVersion(model.Version);
                }
                //}
            }
        }
    }

    public class SqlCeTools : IData
    {
        public Database GetSchema()
        {
            Database db = new Database();
            if (File.Exists("version.txt"))
            {
                db.Version = Convert.ToInt32(File.ReadAllText("version.txt"));
            }
            using (SqlCeConnection cn = new SqlCeConnection(Config.Get.ConnectionString))
            {
                cn.Open();
                using (SqlCeCommand cmd = new SqlCeCommand("SELECT TABLE_NAME  FROM INFORMATION_SCHEMA.TABLES", cn))
                {
                    SqlCeDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        db.Tables.Add(new Table() { Name = rd.GetString(0) });
                    }
                    rd.Close();


                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("SELECT        COLUMN_NAME AS name, DATA_TYPE AS datatype, IS_NULLABLE AS isnull, 0 AS ident,CHARACTER_MAXIMUM_LENGTH AS length ");
                    sb.AppendLine(" FROM            INFORMATION_SCHEMA.COLUMNS ");
                    sb.AppendLine(" WHERE        (TABLE_NAME = '{0}') ");

                    foreach (Table tbl in db.Tables)
                    {
                        cmd.CommandText = string.Format(sb.ToString(), tbl.Name);
                        rd = cmd.ExecuteReader();
                        while (rd.Read())
                        {
                            Column cl = new Column();
                            cl.Collation = "";
                            cl.DataType = rd["datatype"].ToString();
                            if (cl.DataType == "nvarchar")
                            {
                                cl.DataType += "(" + rd["length"].ToString() + ")";
                            }
                            cl.Identity = Convert.ToBoolean(rd["ident"]);
                            cl.Name = rd["name"].ToString();
                            if (rd["isnull"].ToString() == "NO")
                                cl.Nullable = false;
                            else
                                cl.Nullable = true;

                            tbl.Columns.Add(cl);
                        }
                        rd.Close();
                    }

                    foreach (Table tbl in db.Tables)
                    {
                        cmd.CommandText = string.Format("SELECT        INDEX_NAME AS index_name, PRIMARY_KEY AS primary_key, [UNIQUE] AS uniq, [CLUSTERED] AS cluster, COLUMN_NAME AS col  FROM            INFORMATION_SCHEMA.INDEXES WHERE        (TABLE_NAME = '{0}')", tbl.Name);
                        rd = cmd.ExecuteReader();
                        bool yeni = false;
                        while (rd.Read())
                        {

                            Index idx = tbl.Indexes.Where(x => x.Name == rd["index_name"].ToString()).FirstOrDefault();
                            if (idx != null)
                            {
                                yeni = false;
                            }
                            else
                            {
                                idx = new Index();
                                yeni = true;
                            }

                            idx.Clustered = false;
                            idx.Unique = false;
                            idx.PrimaryKey = false;

                            idx.ColumnNames += rd["col"].ToString() + ",";
                            idx.Name = rd["index_name"].ToString();

                            if (rd["cluster"].ToString() == "True")
                                idx.Clustered = true;

                            if (rd["primary_key"].ToString() == "True")
                                idx.PrimaryKey = true;

                            if (rd["uniq"].ToString() == "True")
                                idx.Unique = true;

                            if (yeni)
                            {
                                tbl.Indexes.Add(idx);
                            }
                        }
                        rd.Close();
                    }

                }
                cn.Close();
            }
            return db;
        }

        public bool AddTable(Table table)
        {
            bool sonuc = false;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("CREATE TABLE {0} ( \r\n", table.Name);
            foreach (Column col in table.Columns)
            {
                string nullable = "NULL";
                if (!col.Nullable)
                    nullable = "NOT NULL";

                string identity = "";
                if (col.Identity)
                    identity = "IDENTITY(1,1)";

                if (col.DataType.ToLower().IndexOf("text") > -1)
                    col.DataType = col.DataType.Substring(0, col.DataType.IndexOf('('));

                sb.AppendFormat("	{0} {1} {3} {2}, \r\n", col.Name, col.DataType, nullable, identity);
            }
            using (SqlCeConnection cn = new SqlCeConnection(Config.Get.ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCeCommand cmd = new SqlCeCommand(sb.ToString().Remove(sb.ToString().LastIndexOf(',')) + ")", cn))
                    {
                        sonuc = Convert.ToBoolean(cmd.ExecuteNonQuery());
                    }
                    cn.Close();
                }
                catch (Exception)
                {
                    sonuc = false;
                }
            }

            if (table.Indexes.Count > 0)
                foreach (Index idx in table.Indexes)
                {
                    AddIndex(table, idx);
                }

            return sonuc;
        }

        public bool AddColumn(Table table, Column column)
        {
            bool sonuc = false;
            string def = "";
            if (!column.Nullable && (column.DataType.ToLower().IndexOf("nvarchar") > -1 || column.DataType.ToLower().IndexOf("text") > -1))
                def = "DEFAULT '' ";
            else
                def = "DEFAULT 0";

            string nullable = "NULL";
            if (!column.Nullable)
                nullable = "NOT NULL";

            string identity = "";
            if (column.Identity)
                identity = "IDENTITY(1,1)";

            if (column.DataType.ToLower().IndexOf("text") > -1)
                column.DataType = column.DataType.Substring(0, column.DataType.IndexOf('('));

            string sql = string.Format("Alter Table {0} Add {1} {2} {4} {3} {5}", table.Name, column.Name, column.DataType, nullable, identity, def);
            using (SqlCeConnection cn = new SqlCeConnection(Config.Get.ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCeCommand cmd = new SqlCeCommand(sql, cn))
                    {
                        sonuc = Convert.ToBoolean(cmd.ExecuteNonQuery());
                    }
                    cn.Close();
                }
                catch (Exception)
                {
                    sonuc = false;
                }
            }
            return sonuc;
        }

        public bool AddIndex(Table table, Index index)
        {
            bool sonuc = false;
            string clustered = "NONCLUSTERED";
            if (index.Clustered)
                clustered = "CLUSTERED";

            string unique = "";
            if (index.Unique)
                unique = "UNIQUE";

            string sql = string.Format("CREATE {4} {0} INDEX {1} ON {2} ({3})", clustered, index.Name, table.Name, index.ColumnNames.Remove(index.ColumnNames.LastIndexOf(',')), unique);
            if (index.PrimaryKey)
                sql = string.Format("ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY ({2})", table.Name, index.Name, index.ColumnNames.Remove(index.ColumnNames.LastIndexOf(',')));


            using (SqlCeConnection cn = new SqlCeConnection(Config.Get.ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCeCommand cmd = new SqlCeCommand(sql, cn))
                    {
                        sonuc = Convert.ToBoolean(cmd.ExecuteNonQuery());
                    }
                    cn.Close();
                }
                catch (Exception)
                {
                    sonuc = false;
                }
            }
            return sonuc;
        }

        public bool DropTable(Table table)
        {
            bool sonuc = false;
            string sql = string.Format("DROP TABLE {0}", table.Name);
            using (SqlCeConnection cn = new SqlCeConnection(Config.Get.ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCeCommand cmd = new SqlCeCommand(sql, cn))
                    {
                        sonuc = Convert.ToBoolean(cmd.ExecuteNonQuery());
                    }
                    cn.Close();
                }
                catch (Exception)
                {
                    sonuc = false;
                }
            }
            return sonuc;
        }

        public bool DropColumn(Table table, Column column)
        {
            bool sonuc = false;
            string sql = string.Format("ALTER TABLE {0} DROP COLUMN {1}", table.Name, column.Name);
            using (SqlCeConnection cn = new SqlCeConnection(Config.Get.ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCeCommand cmd = new SqlCeCommand(sql, cn))
                    {
                        sonuc = Convert.ToBoolean(cmd.ExecuteNonQuery());
                    }
                    cn.Close();
                }
                catch (Exception)
                {
                    sonuc = false;
                }
            }
            return sonuc;
        }

        public bool DropIndex(Table table, Index index)
        {
            bool sonuc = false;
            string sql = string.Format("DROP INDEX {0}.{1}", table.Name, index.Name);
            using (SqlCeConnection cn = new SqlCeConnection(Config.Get.ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCeCommand cmd = new SqlCeCommand(sql, cn))
                    {
                        sonuc = Convert.ToBoolean(cmd.ExecuteNonQuery());
                    }
                    cn.Close();
                }
                catch (Exception)
                {
                    sonuc = false;
                }
            }
            return sonuc;
        }

        public bool ChangeCollation(Table table, Column column)
        {
            return true;
        }

        public bool ChangeColumn(Table table, Column column)
        {
            bool sonuc = false;
            string def = "";

            string nullable = "NULL";
            if (!column.Nullable)
                nullable = "NOT NULL";

            string identity = "";
            if (column.Identity)
                identity = "IDENTITY(1,1)";

            if (column.DataType.ToLower().IndexOf("text") > -1)
                column.DataType = column.DataType.Substring(0, column.DataType.IndexOf('('));

            string sql = string.Format("Alter Table {0} Alter Column {1} {2} {4} {3} {5}", table.Name, column.Name, column.DataType, nullable, identity, def);
            using (SqlCeConnection cn = new SqlCeConnection(Config.Get.ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCeCommand cmd = new SqlCeCommand(sql, cn))
                    {
                        sonuc = Convert.ToBoolean(cmd.ExecuteNonQuery());
                    }
                    cn.Close();
                }
                catch (Exception)
                {
                    sonuc = false;
                }
            }
            return sonuc;
        }

        public string GetVersion()
        {
            return "";
        }

        public bool ChangeIndex(Table table, Index index)
        {
            return true;
        }

        public bool ChangeVersion(int version)
        {
            using (StreamWriter sw = new StreamWriter("version.txt"))
            {
                sw.WriteLine(version.ToString());
                sw.Close();
            }
            return true;
        }
    }

    public class SqlServerTools : IData
    {

        public Database GetSchema()
        {
            Database db = new Database();
            if (File.Exists("version.txt"))
            {
                db.Version = Convert.ToInt32(File.ReadAllText("version.txt"));
            }
            using (SqlConnection cn = new SqlConnection(Config.Get.ConnectionString))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand("select name from sysobjects where xtype='U' order by name", cn))
                {
                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        db.Tables.Add(new Table() { Name = rd.GetString(0) });
                    }
                    rd.Close();


                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("SELECT c.name,case when c.precision =0 then (select name from sys.types where user_type_id=c.user_type_id) + '(' + cast(c.max_length as varchar) + ')' else (select name from sys.types where user_type_id=c.user_type_id) end as datatype,c.is_nullable,c.is_identity,c.collation_name ");
                    sb.AppendLine(" FROM sys.columns as c WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM sys.objects ");
                    sb.AppendLine(" WHERE type = 'U' AND name='{0}') order by column_id ");

                    foreach (Table tbl in db.Tables)
                    {
                        cmd.CommandText = string.Format(sb.ToString(), tbl.Name);
                        rd = cmd.ExecuteReader();
                        while (rd.Read())
                        {
                            Column cl = new Column();
                            cl.Collation = rd["collation_name"].ToString();

                            if (rd["datatype"].ToString().IndexOf("image") > -1)
                            {
                                cl.DataType = rd["datatype"].ToString().Substring(0, rd["datatype"].ToString().IndexOf('('));
                            }
                            else
                                cl.DataType = rd["datatype"].ToString().Replace("-1", "MAX");

                            cl.Identity = Convert.ToBoolean(rd["is_identity"]);
                            cl.Name = rd["name"].ToString();
                            cl.Nullable = Convert.ToBoolean(rd["is_nullable"]);
                            tbl.Columns.Add(cl);
                        }
                        rd.Close();
                    }

                    foreach (Table tbl in db.Tables)
                    {
                        cmd.CommandText = string.Format("EXEC sp_helpindex '{0}'", tbl.Name);
                        rd = cmd.ExecuteReader();
                        while (rd.Read())
                        {
                            Index idx = new Index();
                            idx.Clustered = false;
                            idx.Unique = false;
                            idx.PrimaryKey = false;
                            idx.ColumnNames = rd["index_keys"].ToString();
                            idx.Name = rd["index_name"].ToString();

                            string desc = rd["index_description"].ToString();

                            if (desc.ToLower().IndexOf("nonclustered") > -1)
                                idx.Clustered = false;
                            else if (desc.ToLower().IndexOf("clustered") > -1)
                                idx.Clustered = true;

                            if (desc.ToLower().IndexOf("primary key") > -1)
                                idx.PrimaryKey = true;

                            if (desc.ToLower().IndexOf("unique") > -1)
                                idx.Unique = true;

                            tbl.Indexes.Add(idx);
                        }
                        rd.Close();
                    }

                }
                cn.Close();
            }
            return db;
        }

        public bool AddTable(Table table)
        {
            bool sonuc = false;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("CREATE TABLE dbo.{0} ( \r\n", table.Name);
            foreach (Column col in table.Columns)
            {
                string nullable = "NULL";
                if (!col.Nullable)
                    nullable = "NOT NULL";

                string identity = "";
                if (col.Identity)
                    identity = "IDENTITY(1,1)";

                if (col.DataType.ToLower().IndexOf("text") > -1)
                    col.DataType = col.DataType.Substring(0, col.DataType.IndexOf('('));

                sb.AppendFormat("	{0} {1} {3} {2}, \r\n", col.Name, col.DataType, nullable, identity);
            }
            sb.Append(")");

            using (SqlConnection cn = new SqlConnection(Config.Get.ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sb.ToString(), cn))
                    {
                        sonuc = Convert.ToBoolean(cmd.ExecuteNonQuery());
                    }
                    cn.Close();
                }
                catch (Exception)
                {
                    sonuc = false;
                }
            }

            if (table.Indexes.Count > 0)
                foreach (Index idx in table.Indexes)
                {
                    AddIndex(table, idx);
                }

            return sonuc;
        }

        public bool AddColumn(Table table, Column column)
        {
            bool sonuc = false;
            string def = "";
            if (!column.Nullable && (column.DataType.ToLower().IndexOf("varchar") > -1 || column.DataType.ToLower().IndexOf("text") > -1))
                def = "DEFAULT '' ";
            else
                def = "DEFAULT 0";

            string nullable = "NULL";
            if (!column.Nullable)
                nullable = "NOT NULL";

            string identity = "";
            if (column.Identity)
                identity = "IDENTITY(1,1)";

            if (column.DataType.ToLower().IndexOf("text") > -1)
                column.DataType = column.DataType.Substring(0, column.DataType.IndexOf('('));

            string sql = string.Format("Alter Table {0} Add {1} {2} {4} {3} {5}", table.Name, column.Name, column.DataType, nullable, identity, def);
            using (SqlConnection cn = new SqlConnection(Config.Get.ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, cn))
                    {
                        sonuc = Convert.ToBoolean(cmd.ExecuteNonQuery());
                    }
                    cn.Close();
                }
                catch (Exception)
                {
                    sonuc = false;
                }
            }
            return sonuc;
        }

        public bool AddIndex(Table table, Index index)
        {
            bool sonuc = false;
            string clustered = "NONCLUSTERED";
            if (index.Clustered)
                clustered = "CLUSTERED";

            string unique = "";
            if (index.Unique)
                unique = "UNIQUE";

            string sql = string.Format("CREATE {4} {0} INDEX {1} ON {2} ({3})", clustered, index.Name, table.Name, index.ColumnNames, unique);
            if (index.PrimaryKey)
                sql = string.Format("ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY ({2})", table.Name, index.Name, index.ColumnNames);


            using (SqlConnection cn = new SqlConnection(Config.Get.ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, cn))
                    {
                        sonuc = Convert.ToBoolean(cmd.ExecuteNonQuery());
                    }
                    cn.Close();
                }
                catch (Exception)
                {
                    sonuc = false;
                }
            }
            return sonuc;
        }

        public bool DropTable(Table table)
        {
            bool sonuc = false;
            string sql = string.Format("DROP TABLE {0}", table.Name);
            using (SqlConnection cn = new SqlConnection(Config.Get.ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, cn))
                    {
                        sonuc = Convert.ToBoolean(cmd.ExecuteNonQuery());
                    }
                    cn.Close();
                }
                catch (Exception)
                {
                    sonuc = false;
                }
            }
            return sonuc;
        }

        public bool DropColumn(Table table, Column column)
        {
            bool sonuc = false;
            string sql = string.Format("ALTER TABLE {0} DROP COLUMN {1}", table.Name, column.Name);
            using (SqlConnection cn = new SqlConnection(Config.Get.ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, cn))
                    {
                        sonuc = Convert.ToBoolean(cmd.ExecuteNonQuery());
                    }
                    cn.Close();
                }
                catch (Exception)
                {
                    sonuc = false;
                }
            }
            return sonuc;
        }

        public bool DropIndex(Table table, Index index)
        {
            bool sonuc = false;
            string sql = string.Format("DROP INDEX {0}.{1}", table.Name, index.Name);
            using (SqlConnection cn = new SqlConnection(Config.Get.ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, cn))
                    {
                        sonuc = Convert.ToBoolean(cmd.ExecuteNonQuery());
                    }
                    cn.Close();
                }
                catch (Exception)
                {
                    sonuc = false;
                }
            }
            return sonuc;
        }

        public bool ChangeCollation(Table table, Column column)
        {
            return true;
        }

        private void DropConstraint(Table table)
        {
            using (SqlConnection cn = new SqlConnection(Config.Get.ConnectionString))
            {
                try
                {
                    cn.Open();
                    List<string> _cons = new List<string>();
                    using (SqlCommand cmd = new SqlCommand(string.Format("SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint FROM sys.objects WHERE type_desc LIKE 'DEFAULT_CONSTRAINT' and OBJECT_NAME(parent_object_id)='{0}'", table.Name), cn))
                    {
                        SqlDataReader rd = cmd.ExecuteReader();
                        while (rd.Read())
                        {
                            _cons.Add(rd.GetString(0));
                        }
                        rd.Close();

                        foreach (string item in _cons)
                        {
                            cmd.CommandText = string.Format("ALTER TABLE {0} DROP CONSTRAINT {1}", table.Name, item);
                            cmd.ExecuteNonQuery();
                        }
                    }
                    cn.Close();
                }
                catch (Exception)
                {
                }
            }
        }

        public bool ChangeColumn(Table table, Column column)
        {
            DropConstraint(table);
            bool sonuc = false;
            string def = "";

            string nullable = "NULL";
            if (!column.Nullable)
                nullable = "NOT NULL";

            string identity = "";
            if (column.Identity)
                identity = "IDENTITY(1,1)";

            if (column.DataType.ToLower().IndexOf("text") > -1)
                column.DataType = column.DataType.Substring(0, column.DataType.IndexOf('('));

            string sql = string.Format("Alter Table {0} Alter Column {1} {2} {4} {3} {5}", table.Name, column.Name, column.DataType, nullable, identity, def);
            using (SqlConnection cn = new SqlConnection(Config.Get.ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, cn))
                    {
                        sonuc = Convert.ToBoolean(cmd.ExecuteNonQuery());
                    }
                    cn.Close();
                }
                catch (Exception)
                {
                    sonuc = false;
                }
            }
            return sonuc;
        }

        public string GetVersion()
        {
            return "";
        }

        public bool ChangeIndex(Table table, Index index)
        {
            return true;
        }

        public bool ChangeVersion(int version)
        {
            using (StreamWriter sw = new StreamWriter("version.txt"))
            {
                sw.WriteLine(version.ToString());
                sw.Close();
            }
            return true;
        }
    }
}
